package cn.iocoder.yudao.module.erp.dal.mysql.dp;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.module.erp.dal.dataobject.dp.DpDO;
import cn.iocoder.yudao.module.erp.dal.dataobject.dp.DpYwDO;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import org.apache.ibatis.annotations.Mapper;
import cn.iocoder.yudao.module.erp.controller.admin.dp.vo.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * ERP总制单 Mapper
 * @author 天使管理员
 */
@Mapper
public interface DpMapper extends BaseMapperX<DpDO> {

    default PageResult<DpDO> selectPage(DpPageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<DpDO>()
                .likeIfPresent(DpDO::getId, reqVO.getId())
                .inIfPresent(DpDO::getId,reqVO.getIds())
                .likeIfPresent(DpDO::getNo, reqVO.getNo())
                .eqIfPresent(DpDO::getStoreType, reqVO.getStoreType())
                .eqIfPresent(DpDO::getInCount, reqVO.getInCount())
                .likeIfPresent(DpDO::getInName, reqVO.getInName())
                .eqIfPresent(DpDO::getInVolume, reqVO.getInVolume())
                .eqIfPresent(DpDO::getWeight, reqVO.getWeight())
                .eqIfPresent(DpDO::getDensity, reqVO.getDensity())
                .eqIfPresent(DpDO::getDeliveryPhone, reqVO.getDeliveryPhone())
                .likeIfPresent(DpDO::getDeliveryName, reqVO.getDeliveryName())
                .eqIfPresent(DpDO::getEarpayAmount, reqVO.getEarpayAmount())
                .eqIfPresent(DpDO::getInPic, reqVO.getInPic())
                .eqIfPresent(DpDO::getLine, reqVO.getLine())
                .eqIfPresent(DpDO::getFile, reqVO.getFile())
                .eqIfPresent(DpDO::getNote, reqVO.getNote())
                .likeIfPresent(DpDO::getShipper, reqVO.getShipper())
                .eqIfPresent(DpDO::getDestination, reqVO.getDestination())
                .eqIfPresent(DpDO::getConsignee, reqVO.getConsignee())
                .eqIfPresent(DpDO::getAdvancePayPack, reqVO.getAdvancePayPack())
                .eqIfPresent(DpDO::getAdvancePayPackUp, reqVO.getAdvancePayPackUp())
                .eqIfPresent(DpDO::getAdvancePayChinaFreight, reqVO.getAdvancePayChinaFreight())
                .eqIfPresent(DpDO::getAdvancePayForGoods, reqVO.getAdvancePayForGoods())
                .eqIfPresent(DpDO::getCollecting, reqVO.getCollecting())
                .eqIfPresent(DpDO::getCustomerRebate, reqVO.getCustomerRebate())
                .eqIfPresent(DpDO::getBusinessRebate, reqVO.getBusinessRebate())
                .eqIfPresent(DpDO::getStatus, reqVO.getStatus())
                .eqIfPresent(DpDO::getInventoryStatus, reqVO.getInventoryStatus())
                .eqIfPresent(DpDO::getIsSplit, reqVO.getIsSplit())
                .betweenIfPresent(DpDO::getCreateTime, reqVO.getCreateTime())
                .betweenIfPresent(DpDO::getChooseDate, reqVO.getChooseDateStr())
                .inIfPresent(DpDO::getStatus,0,1)
                .orderByDesc(DpDO::getId));
    }

    default PageResult<DpDO> selectByShipper(DpPageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<DpDO>()
                .eqIfPresent(DpDO::getShipper, reqVO.getShipper())
                .inIfPresent(DpDO::getStatus,0,1,3)
                .orderByAsc(DpDO::getChooseDate));
    }


    default PageResult<DpDO> selectReturnPage(DpPageReqVO reqVO) {
        return selectPage(reqVO, new LambdaQueryWrapperX<DpDO>()
                .likeIfPresent(DpDO::getId, reqVO.getId())
                .likeIfPresent(DpDO::getNo, reqVO.getNo())
                .eqIfPresent(DpDO::getStoreType, reqVO.getStoreType())
                .eqIfPresent(DpDO::getInCount, reqVO.getInCount())
                .likeIfPresent(DpDO::getInName, reqVO.getInName())
                .eqIfPresent(DpDO::getInVolume, reqVO.getInVolume())
                .eqIfPresent(DpDO::getWeight, reqVO.getWeight())
                .eqIfPresent(DpDO::getDensity, reqVO.getDensity())
                .eqIfPresent(DpDO::getDeliveryPhone, reqVO.getDeliveryPhone())
                .likeIfPresent(DpDO::getDeliveryName, reqVO.getDeliveryName())
                .eqIfPresent(DpDO::getEarpayAmount, reqVO.getEarpayAmount())
                .eqIfPresent(DpDO::getInPic, reqVO.getInPic())
                .eqIfPresent(DpDO::getLine, reqVO.getLine())
                .eqIfPresent(DpDO::getFile, reqVO.getFile())
                .eqIfPresent(DpDO::getNote, reqVO.getNote())
                .eqIfPresent(DpDO::getShipper, reqVO.getShipper())
                .eqIfPresent(DpDO::getDestination, reqVO.getDestination())
                .eqIfPresent(DpDO::getConsignee, reqVO.getConsignee())
                .eqIfPresent(DpDO::getAdvancePayPack, reqVO.getAdvancePayPack())
                .eqIfPresent(DpDO::getAdvancePayPackUp, reqVO.getAdvancePayPackUp())
                .eqIfPresent(DpDO::getAdvancePayChinaFreight, reqVO.getAdvancePayChinaFreight())
                .eqIfPresent(DpDO::getAdvancePayForGoods, reqVO.getAdvancePayForGoods())
                .eqIfPresent(DpDO::getCollecting, reqVO.getCollecting())
                .eqIfPresent(DpDO::getCustomerRebate, reqVO.getCustomerRebate())
                .eqIfPresent(DpDO::getBusinessRebate, reqVO.getBusinessRebate())
                .eqIfPresent(DpDO::getStatus, reqVO.getStatus())
                .eqIfPresent(DpDO::getInventoryStatus, reqVO.getInventoryStatus())
                .eqIfPresent(DpDO::getIsSplit, reqVO.getIsSplit())
                .betweenIfPresent(DpDO::getCreateTime, reqVO.getCreateTime())
                .betweenIfPresent(DpDO::getChooseDate, reqVO.getChooseDateStr())
                .eqIfPresent(DpDO::getStatus,reqVO.getStatus())
                .orderByDesc(DpDO::getId));
    }

    default PageResult<DpDO> shipperDate(DpPageReqVO reqVO) {
        return selectPage(reqVO, new QueryWrapper<DpDO>()
                .select("shipper,SUM(total_amount) AS total_amount,SUM(received_fare) AS received_fare,SUM(debt_total) AS debt_total").in("status",0,1,3).like(reqVO.getShipper()!=null,"shipper",reqVO.getShipper()).groupBy("shipper"));
    }

    @Select("SELECT SUM(weight) FROM erp_dp WHERE status!=2 and choose_date BETWEEN #{startTime} AND #{endTime}")
    Double getTotalWeightByTimeRange(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT COUNT(DISTINCT creator) FROM erp_dp dp " +
            "WHERE dp.choose_date >= #{startTime} and dp.status!=2 " +
            "AND NOT EXISTS ( " +
            "    SELECT 1 FROM erp_dp dp2 " +
            "    WHERE dp2.creator = dp.creator and dp2.status!=2 " +
            "    AND dp2.choose_date < #{startTime} " +
            ")")
    Integer getNewCustomerCount(@Param("startTime") String startTime);

    @Select("SELECT SUM(debt_total) as yearOfDebtTotal FROM erp_dp WHERE status!=2 and choose_date >= #{startTime}")
    Double getDebtTotalForYear(@Param("startTime") String startTime);

    @Select("select (count(distinct a.shipper) - (select count(distinct b.shipper) from erp_dp b where b.status!=2 and b.choose_date >= #{startTime} - 30)) from erp_dp a where a.status!=2")
    Integer getMonthNotShip(@Param("startTime") String startTime);

    @Select("select (count(distinct a.shipper)) from erp_dp a where a.status!=2")
    Integer getMaxship(@Param("startTime") String startTime);

    @Select("select count(distinct b.shipper) from erp_dp b where b.status!=2 and b.choose_date >= #{startTime} - 30")
    Integer getMinShip(@Param("startTime") String startTime);

    default Integer updatePickStatusById(List<String> ids){
        if (ids == null || ids.isEmpty()) {
            return 0;
        }
        return update(new LambdaUpdateWrapper<DpDO>()
                .in(DpDO::getId, ids) // 条件：id 在传入的列表中
                .set(DpDO::getPickStatus, 1)); // 设置 pickStatus 为 1
    };

    @Update("UPDATE ${tableName} SET pick_status = #{pickStatus},sign_num = #{signNum} WHERE id=#{id}")
    Integer updatePickStatusBytableName(@Param("tableName") String tableName,
                                        @Param("id") String id,
                                        @Param("pickStatus") Integer pickStatus,
                                        @Param("signNum") Integer signNum);

    @Update("UPDATE ${tableName} SET pick_status = #{pickStatus},sign_num = #{signNum},arr_status= #{ArrStatus} WHERE id=#{id}")
    Integer updateArrStatusBytableName(@Param("tableName") String tableName,
                                        @Param("id") String id,
                                        @Param("pickStatus") Integer pickStatus,
                                        @Param("signNum") Integer signNum,@Param("ArrStatus") Integer ArrStatus);

    @Select("SELECT * FROM ${tableName} WHERE id = #{id}")
    DpDO selectDpByid(@Param("tableName") String tableName,@Param("id") String id);

    @Select("SELECT " +
            "IFNULL(SUM(total_amount), 0) AS totalAmount, " +
            "IFNULL(SUM(received_fare), 0) AS receivedFare, " +
            "IFNULL((SUM(total_amount) - SUM(received_fare)), 0) AS debtTotal, " +
            "IFNULL(SUM(write_expenses), 0) AS writeExpenses, " +
            "IFNULL(SUM(is_write_off), 0) AS isWriteOff, " +
            "IFNULL(SUM(not_write_off), 0) AS notWriteOff, " +
            "IFNULL(SUM(customer_rebate), 0) AS customerRebate, " +
            "IFNULL(SUM(is_point), 0) AS isPoint, " +
            "IFNULL(SUM(not_point), 0) AS notPoint " +
            "FROM erp_dp " +
            "WHERE status in(0,1,3) and shipper = #{shipper}")
    DpDO getDpFareCount(@Param("shipper") String shipper);

//    default List<DpDO> searchSipperDpList(String sipper){
//        QueryWrapper<DpDO> queryWrapper = new QueryWrapper<>();
//        queryWrapper.like("shipper", sipper);
//        queryWrapper.gt("debt_total",0.00);
//        return selectList(queryWrapper);
//    }


    List<DpDO> searchSipperDpList(@Param("shipper") String shipper, @Param("no") String[] no, @Param("prepaidStatus") Integer prepaidStatus);

    @Update("UPDATE erp_dp SET received_fare = received_fare+#{amount},debt_total = debt_total-#{amount} WHERE id=#{id}")
    Integer dpPay(@Param("amount") Double money,@Param("id") String id);

    @Update("UPDATE erp_dp SET claim_amount = claim_amount+#{amount},debt_total = debt_total-#{amount} WHERE id=#{id}")
    Integer dpClaim(@Param("amount") Double money,@Param("id") String id);

    @Select("SELECT distinct shipper FROM erp_dp WHERE shipper LIKE CONCAT('%', #{shipper}, '%')")
    List<DpDO> getNoByShipper(@Param("shipper") String shipper);


    default void updateByNo(DpDO dpDO) {
         update(dpDO, new LambdaUpdateWrapper<DpDO>()
                .eq(DpDO::getNo, dpDO.getNo()) // 根据 No 字段匹配
                .set(dpDO.getIsWriteOff() != null, DpDO::getIsWriteOff, dpDO.getIsWriteOff()) // 更新 isWriteOff
                .set(dpDO.getNotWriteOff() != null, DpDO::getNotWriteOff, dpDO.getNotWriteOff())
                .set(dpDO.getIsPoint() != null, DpDO::getIsPoint, dpDO.getIsPoint())
                .set(dpDO.getNotPoint() != null, DpDO::getNotPoint, dpDO.getNotPoint())
                .setSql("debt_total = total_amount - " + dpDO.getReceivedFare()) // 直接在 SQL 中计算 debt_total
        );
    }

    void batchUpdateById(@Param("list") List<DpDO> dpList);

    @Update("UPDATE erp_dp SET inventory_status = 2 WHERE id=#{id}")
    Integer enterDpStart(@Param("id") String id);

}